package com.saozhu.Dao;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.saozhu.pojo.Client;
import com.saozhu.pojo.Good;
import com.saozhu.pojo.Order;
import com.saozhu.pojo.Type;

public class Dao {
	
	public Dao() {}
	
	public Good getGood(int good_id) {
		Good good = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		String sql = "select * from `order_management`.`goods` where `id` = ?;";
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);

			stmt.setInt(1, good_id);

			rs = stmt.executeQuery();
			
			while (rs.next()) {
				String name = rs.getString("name");
				int number = rs.getInt("number");
				float price = rs.getFloat("price");
				String open = rs.getString("open");
				int type_id = rs.getInt("type_id");	
				
				good = new Good(good_id, name, number, price, open, type_id);
			}
			
			return good;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, rs);
		}
		return good;
	}
	
	public List<Good> getGoods() {
		List<Good> goods = new ArrayList<Good>();
		Good good = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		String sql = "select * from `order_management`.`goods` order by open;";
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);

			rs = stmt.executeQuery();
			
			while (rs.next()) {
				int good_id = rs.getInt("id");
				String name = rs.getString("name");
				int number = rs.getInt("number");
				float price = rs.getFloat("price");
				String open = rs.getString("open");
				int type_id = rs.getInt("type_id");	
				
				good = new Good(good_id, name, number, price, open, type_id);
				goods.add(good);
			}
			
			return goods;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, rs);
		}
		return goods;
	}
	
	public List<Client> getClients() {
		List<Client> clients = new ArrayList<Client>();
		Client client = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		String sql = "select * from `order_management`.`client` order by credit;";
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);

			rs = stmt.executeQuery();
			
			while (rs.next()) {
				int client_id = rs.getInt("id");
				String name = rs.getString("name");
				String phone = rs.getString("phone");
				int credit = rs.getInt("credit");
				
				client = new Client(client_id, name, phone, credit);
				clients.add(client);
			}
			
			return clients;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, rs);
		}
		return clients;
	}
	
	public List<Order> getOrders() {
		List<Order> orders = new ArrayList<Order>();
		Order order = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		String sql = "select * from `order_management`.`order` order by open;";
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);

			rs = stmt.executeQuery();
			
			while (rs.next()) {
				int order_id = rs.getInt("id");
				String order_code = rs.getString("order_code");
				String pay = rs.getString("pay");
				float price = rs.getFloat("price");
				int goods_num = rs.getInt("goods_num");
				String finish = rs.getString("finish");
				String open = rs.getString("open");
				int goods_id = rs.getInt("goods_id");
				int user_id = rs.getInt("user_id");
				int receive_information_id = rs.getInt("receive_information_id");
				
				order = new Order(order_id, order_code, pay, price, goods_num, finish, open, goods_id, user_id, receive_information_id);
				orders.add(order);
			}
			
			return orders;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, rs);
		}
		return orders;
	}
	
	public List<Type> getTypes() {
		List<Type> types = new ArrayList<Type>();
		Type type = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		String sql = "select * from `order_management`.`type`;";
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);

			rs = stmt.executeQuery();
			
			while (rs.next()) {
				int type_id = rs.getInt("id");
				String name = rs.getString("name");
				
				type = new Type(type_id, name);
				types.add(type);
			}
			
			return types;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, rs);
		}
		return types;
	}
	
	public void UpdateGood(Good good) {
		Connection conn = null;
		PreparedStatement stmt = null;

		//String sqlInsert = "INSERT INTO `order_management`.`goods`(`name`,`number`,`price`,open`,`type_id`) VALUES(?,?,?,?,?)";
		String sqlUpdate = "UPDATE `order_management`.`goods`o SET `name` = ?,`number` = ?,`price` = ?,`open` = ?,`type_id` = ? WHERE `id` = ?";
		try {
			conn = getConnection();

			String sql = sqlUpdate;
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, good.getName());
			stmt.setInt(2, good.getNumber());
			stmt.setFloat(3, good.getPrice());
			stmt.setString(4, good.getOpen());
			stmt.setInt(5, good.getType_id());
			stmt.setInt(6, good.getId());
			
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, null);
		}
	}

	public void AddGood(Good good) {
		Connection conn = null;
		PreparedStatement stmt = null;

		String sqlInsert = "INSERT INTO `order_management`.`goods`(`name`,`number`,`price`,`open`,`type_id`) VALUES(?,?,?,?,?)";
		//String sqlUpdate = "UPDATE `besuper`.`question` SET `name` = ?,`number` = ?,`price` = ?,`open` = ?,`type_id` = ? WHERE `id` = ?";
		//boolean isUpdate = good.getId() != -1;
		try {
			conn = getConnection();

			String sql = sqlInsert;
			//if (isUpdate) sql = sqlUpdate;
			
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, good.getName());
			stmt.setInt(2, good.getNumber());
			stmt.setFloat(3, good.getPrice());
			stmt.setString(4, good.getOpen());
			stmt.setInt(5, good.getType_id());
			//if (isUpdate) stmt.setInt(6, good.getId());
			
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, null);
		}
	}
	
	public void SetGoodOpen(Good good, String str) {
		Connection conn = null;
		PreparedStatement stmt = null;

		String sql = "update `order_management`.`goods` set `open` = ? where `id` = ?;";
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
            stmt.setString(1, str);
			stmt.setInt(2, good.getId());
			
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, null);
		}
	}
	
	public static Connection getConnection() throws SQLException {

		try {
			Class.forName(getProperty("jdbc.driver"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection conn;
		String url = getProperty("url");
		String user = getProperty("user");
		String password = getProperty("password");

		conn = DriverManager.getConnection(url, user, password);
		return conn;
	}

	private static String getProperty(String key) {
		Properties props = new Properties();
		try {
			props.load(new FileInputStream("conn.properties"));

			return props.getProperty(key);

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		return null;
	}

	public static void clean(Connection conn, Statement stmt, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}
